Step 3: Relationships Explained — Amazon (Shopping)
Relationship Map Overview
The Amazon database handles 25+ key relationships optimized for high-volume transactions, warehouse inventory checks, cart checkouts, and shipping updates.
1. Catalog & Product Relationships
categories → categories (Self-Referential — 1:N)
Maintains the catalog taxonomy hierarchy (e.g. Electronics -> Computers -> Laptops).
categories.category_id ←→ categories.parent_category_id
products → product_variants (1:N)
A core relationship: master products (e.g. "Kindle Paperwhite") can have multiple variants based on attributes (e.g. "8GB, Black", "16GB, Denim").
products.product_id ←→ product_variants.product_id
Cascade Rule: Deleting a product must cascade-delete all variants.
products → reviews (1:N)
Product pages load associated customer reviews.
products.product_id ←→ reviews.product_id
2. Inventory & Warehouse Management
product_variants ↔ fulfillment_centers via inventories (M:N)
Many warehouses store stock for many product variants.
product_variants.variant_id ←→ inventories.variant_id
fulfillment_centers.center_id ←→ inventories.center_id
Concurrency Note: Inventory reads and reservations must happen in sub-milliseconds to avoid double-selling items during flash sales.
3. Cart & Order Checkouts
users ↔ product_variants via cart_items (M:N)
A user can add many variants to their cart, and a variant can exist in many users' carts.
users.user_id ←→ carts.user_id
carts.cart_id ←→ cart_items.cart_id
product_variants.variant_id ←→ cart_items.variant_id
orders → order_items (1:N)
Captured purchase logs.
orders.order_id ←→ order_items.order_id
product_variants.variant_id ←→ order_items.variant_id
Data Integrity Rule: order_items stores a copy of the variant's price at the time of order placement (price_per_unit), maintaining order history accuracy.
4. Shipping & Logistics Tracking
orders → order_shipments (1:N)
An order can be divided into multiple packages (shipments) if items are dispatched from different warehouses (fulfillment centers).
orders.order_id ←→ order_shipments.order_id
order_shipments → shipment_tracking_logs (1:N)
Tracks package location and delivery steps in real-time.
order_shipments.shipment_id ←→ shipment_tracking_logs.shipment_id
Relationship Summary Table
| Relationship | Cardinality | Parent Entity | Child Entity | Junction Entity | Cascade Action |
|---|---|---|---|---|---|
| Category Hierarchy | 1:N (Self) | categories |
categories |
— (Self-Ref parent_id) |
Set Null |
| Product Variants | 1:N | products |
product_variants |
— | Cascade Delete |
| Product Media | 1:N | products |
product_media |
— | Cascade Delete |
| Warehouse Inventory | M:N | product_variants |
fulfillment_centers |
inventories |
Restrict |
| Shopping Cart Items | M:N | carts |
product_variants |
cart_items |
Cascade Delete |
| Customer Orders | 1:N | users |
orders |
— | Restrict |
| Order Items | 1:N | orders |
product_variants |
order_items |
Restrict |
| Logistics Shipments | 1:N | orders |
order_shipments |
— | Restrict |
| Transit Tracking Logs | 1:N | order_shipments |
shipment_tracking_logs |
— | Cascade Delete |
| Product Reviews | 1:N | products |
reviews |
— | Cascade Delete |
| Review Helpful Votes | M:N | reviews |
users |
review_helpful_votes |
Cascade Delete |
| Coupon Logging | M:N | coupons |
users |
user_coupons |
Restrict |
| Customer Wishlist | M:N | wishlists |
product_variants |
wishlist_items |
Cascade Delete |